Automatic Workload Driven Index Defragmentation

Queries that scan a B-Tree index can suffer significant I/O performance degradation due to index fragmentation. The task of determining if an index should be defragmented is challenging for database administrators (DBAs) since today’s database engines offer no support for quantifying the impact of defragmenting an index on query I/O performance. Furthermore, DBMSs only support defragmentation at the granularity of an entire B-Tree, which can be very restrictive since defragmentation is an expensive operation and workloads typically access different ranges of an index non-uniformly. We have developed techniques to address the above two challenges, and implemented a prototype of automatic workload driven index defragmentation functionality in Microsoft SQL Server. We demonstrate this functionality by showing (a) how the system tracks the potential benefit of defragmenting an index on I/O performance at low overhead, (b) the ability to defragment a range of a B-Tree index online, and (c) how the cost/benefit trade-off can be controlled in a policy driven manner to enable automatic workload driven index defragmentation requiring minimal DBA intervention.